package com.surelution.projects.crm
import org.codehaus.groovy.grails.commons.ConfigurationHolder
import groovy.sql.Sql
import org.codehaus.groovy.grails.commons.ApplicationHolder
import org.springframework.context.ApplicationContext

import groovy.time.TimeCategory

import java.text.NumberFormat;
import java.text.SimpleDateFormat
import java.util.Date;
/**
 * 
 * @author Miracle
 *
 */

class GasListController {

    static allowedMethods = [save: "POST", update: "POST", delete: "POST"]
	def exportService
	
    def index = {
        redirect(action: "report")
    }
	def GasReportService
/**
 * 	加油站日报（油品非油品）
 */
	def dayReport = {
		def startDay = new Date()
		def areaIds = []
		def mapList,gasMonthFinish,noGasMonthFinish,endDay,month
		def selectType = Long.parseLong("0")
		if (params.startDay&&params.stationArea){
			startDay = params.startDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			month = parseDate(params.startDay)
			use(TimeCategory) {
				endDay = startDay + 1.days
			}
			mapList = dayStationReport(startDay,areaIds,selectType)
			gasMonthFinish = gasMFinishRate(month[0], endDay, areaIds)
			noGasMonthFinish = noGasMFinishRate(month[0], endDay, areaIds)
			mapList.sort {a,b->
				(b.get("allPlan")?b.get("allPlan"):0) <=> (a.get("allPlan")?a.get("allPlan"):0)
			}
		}
		[startDay:startDay,mapList:mapList,stationArea:areaIds,gasMonthFinish:gasMonthFinish,noGasMonthFinish:noGasMonthFinish]
	}
	
	def dayStationReport(Date startDay,areaIds,selectType){
		def result,endDay
		def mapGasList = new ArrayList<HashMap>()
		def mapNoGasList = new ArrayList<HashMap>()
		def mapList = new ArrayList<HashMap>()
		Date month = new Date(startDay.year,startDay.month,1)
		use(TimeCategory) {
			endDay = startDay + 1.days
		}
		result = gasReport(startDay,endDay,selectType,areaIds)
		def planResult = GasPlanReport(month,endDay,areaIds,'M')
		if (result == 0){
			flash.message = "${message(code: 'default.nofound.message')}"
			redirect(action: "dayReport")
		}
		result.each{
			planResult.each{p->
				if(p[1]==it[1]){
					def map = new HashMap()
					map.put("area", it[0])
					map.put("station", it[1])
					map.put("0号", parseNumber(((it[2])/1000).toString(),1))
					map.put("93号", parseNumber((it[3]/1000).toString(),1))
					map.put("97号", parseNumber((it[4]/1000).toString(),1))
					map.put("0号1", parseNumber((it[5]/1000).toString(),1))
					map.put("all", parseNumber((it[it.size()-1]/1000).toString(),1))
					if(p[4]){
						map.put("plan", p[4])
						map.put("allPlan", parseNumber((p[8]).toString(),1))
					}
					else {
						map.put("plan", 0)
						map.put("allPlan", 100)
					}
					mapGasList.add(map)
				}	
			}
		}
			def	noGasList = GasReportService.noGasReport(startDay,startDay,areaIds)
			def	noGasPlan = GasReportService.noGasPlanReport(month,endDay,areaIds)
			noGasPlan.each{n->
				int flag = GasReportService.isExist(noGasList,n[1],1)
				def map = new HashMap()
				def i = 2
				map.put("station", n[1])
				if (flag == 1){
					
					noGasList.each{
						if(n[1]==it[1]){
							NoGasTypeCategory.list().each {st->
								map.put(st.name.toString(), parseNumber(it[i].toString(),2))
								i++
							}
							map.put('noGasAll', parseNumber((it[it.size()-3]).toString(),1))
							def n5 = n[5]?n[5]:0
							map.put("noGasPlan", n[2]?n[2]:0)
							map.put("allPlan", parseNumber(n5.toString(),2))
							mapNoGasList.add(map)
						}
					}
				}
				else{
					NoGasTypeCategory.list().each {st->
						map.put(st.name.toString(), 0)
						i++
					}
					map.put('noGasAll', 0)
					def n5 = n[5]?n[5]:0
					map.put("noGasPlan", n[2]?n[2]:0)
					map.put("allPlan", parseNumber(n5.toString(),2))
					mapNoGasList.add(map)
				}
			}
			mapGasList.each{
				int flag = GasReportService.isExistHash(mapNoGasList,it.get("station"))
				def map = new HashMap()
				def i = 2
				map.put("area", it.get("area"))
				map.put("station", it.get("station").shortName)
				map.put("0号", it.get("0号"))
				map.put("93号", it.get("93号"))
				map.put("97号", it.get("97号"))
				map.put("0号1", it.get("0号1"))
				map.put("all", it.get("all"))
				map.put("plan", it.get("plan"))
				map.put("allPlan", it.get("allPlan"))
				if(flag!=0){
					mapNoGasList.each{n->
						if(n.get("station")==it.get("station")){
						NoGasTypeCategory.list().each {st->
							map.put(st.name.toString(), n.get(st.name))
							i++
						}
						map.put('noGasAll', n.get("noGasAll"))
						map.put("noGasPlan", n.get("noGasPlan"))
						map.put("allNoGasPlan", n.get("allPlan"))	
						mapList.add(map)
						}
					}
				}
				else{
					NoGasTypeCategory.list().each {st->
						map.put(st.name.toString(), 0)
						i++
					}
					map.put('noGasAll', 0)
					map.put("noGasPlan", 0)
					map.put("allNoGasPlan", 0)
					mapList.add(map)
				}
			}
		mapList.sort{
			it.get("area")
		}
		mapList.sort {a,b->
			(b.get("allPlan")?b.get("allPlan"):0) <=> (a.get("allPlan")?a.get("allPlan"):0)
		}
		println mapList
		return mapList
	}
	
	
	def dayExportReport = {
		def startDay
		def areaIds = []
		def mapList
		def selectType = Long.parseLong("0")
		if (params.startDay&&params.stationArea){
			startDay = params.startDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			mapList = dayStationReport(startDay,areaIds,selectType)
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=dayReport.xls")
			List fields = ['area','station','0号','93号','97号','all','plan','allPlan']
			Map labels = ['area':"片区",'station':"站名",'0号':"0#",'93号':"93#",'97号':"97#",'all':"合计",'plan':"本月计划量",'allPlan':"计划完成率"]
			NoGasTypeCategory.list().each {st->
				fields.add((st.name).toString())
				labels.put((st.name).toString(), st.name+"(元)")
			}
			fields.add("noGasAll")
			labels.put("noGasAll", "收入合计")
			fields.add("noGasPlan")
			labels.put("noGasPlan", "计划量")
			fields.add("allNoGasPlan")
			labels.put("allNoGasPlan", "计划完成率")
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
	}
	
/**
 * 加油站月报	
 */
	def monthReport = {
		def month =[new Date(),new Date(),new Date()]
		def gasMonthFinish,gasYearFinish,noGasMonthFinish,noGasYearFinish
		def startDay = new Date()
		def endDay = new Date()
		def areaIds = []
		def mapList 
		def days = params.days
		def selectType = Long.parseLong("0")
		if (params.month&&params.stationArea){
			month = parseDate(params.month)
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			mapList = monthGasReport(month[0], month[1], month[2], areaIds, selectType)
			Date endYear
			use(TimeCategory) {
				endYear = month[2] + 12.months
			}
			gasMonthFinish = gasMFinishRate(month[0], month[1], areaIds)
			gasYearFinish = gasYFinishRate(month[2],endYear,month[1],areaIds)
			noGasMonthFinish = noGasMFinishRate(month[0], month[1], areaIds)
			noGasYearFinish = noGasYFinishRate(month[2],endYear,month[1],areaIds)
		}
		println "return page:${mapList}"
		[month:month[1],mapList:mapList,stationArea:areaIds,startDay:month[0],endDay:month[1],gasMonthFinish:gasMonthFinish,gasYearFinish:gasYearFinish,noGasYearFinish:noGasYearFinish,noGasMonthFinish:noGasMonthFinish]
	}
	
	/**
	 * 输入月份后，分析月度、年度完成率时的日期,返回值分别为月度开始日期、月度结束日期、年度开始、年度结束日期
	 * 月度取当月份结算日，年度取上一年最后一个结算日
	 * @param month
	 * @return
	 */
	def parseDate(Date month){
		Date year
		def m = MonthClear.createCriteria().get{
			eq("year", month.year+1900)
		}
		def endDay = new Date(month.year, month.month+1, -m.clearDay)
		def startDay = new Date(month.year, month.month, -m.clearDay)
		if (startDay.year==endDay.year){
			use(TimeCategory) {
				year = startDay - (endDay.month).months
			}
		}
		else year = startDay
		def months = []
		months.add(startDay)
		months.add(endDay)
		months.add(year)
		return months
	}
	
	def monthGasReport(Date startDay, Date endDay, Date year, areaIds, selectType){
		Date month = new Date(startDay.year,startDay.month,1)
		def planResult = GasPlanReport(startDay,endDay,areaIds,'M')
		def planYearResult = GasPlanReport(year,endDay,areaIds,'Y')
		println "planResult:${planResult}"
		println "planYearResult:${planYearResult}"
		if(planResult == 0){
			flash.message = "${message(code: 'default.nofound.message')}"
			redirect(action: "monthReport")
		}
		def mapGasList = new ArrayList<HashMap>()
		planYearResult.each{
			int flag = GasReportService.isExist(planResult,it[1],1)
			def map = new HashMap()
			map.put("area",it[0])
			map.put("station",it[1])
			map.put("gasPlan",it[2])
			map.put("fuelPlan",it[3])
			map.put("AllPlan",it[4])
			map.put("yearFinish",parseNumber(it[8].toString(),1))
			if(flag == 1){
				planResult.each{p->
					if(p[1] == it[1]){
						map.put("gasSale",parseNumber(p[5].toString(),1))
						map.put("fuelSale",parseNumber(p[6].toString(),1))
						map.put("AllSale",parseNumber(p[7].toString(),1))
						map.put("monthFinish",parseNumber(p[8].toString(),1))
						mapGasList.add(map)
					}
				}
			}
			else {
				map.put("gasSale",0)
				map.put("fuelSale",0)
				map.put("AllSale",0)
				map.put("monthFinish",0)
				mapGasList.add(map)
			}
		}
		def planNoGasResult = GasReportService.noGasPlanReport(startDay,endDay,areaIds)
		def planNoGasYearResult = GasReportService.noGasPlanReport(year,endDay,areaIds)
		def mapNoGasList = new ArrayList<HashMap>()
		if(planNoGasYearResult!=0 && planNoGasResult!=0){
			planNoGasYearResult.each{
				int flag = GasReportService.isExist(planNoGasResult,it[1],1)
				def map = new HashMap()
				map.put("area",it[0])
				map.put("station",it[1])
				map.put("noGasYearFinish", parseNumber(it[5].toString(),1))
				if(flag == 1){
					planNoGasResult.each{p->
						if(p[1] == it[1]){
							map.put("noGasAll",p[3])
							map.put("noGasMaoLi",p[4])
							map.put("noGasMonthFinish",parseNumber(p[5].toString(),1))
							mapNoGasList.add(map)
						}
					}
				}
				else{
					map.put("noGasAll",0)
					map.put("noGasMaoLi",0)
					map.put("noGasMonthFinish",0)
					mapNoGasList.add(map)
				}
			}
		}
		def mapNoGasAndGas = new ArrayList<HashMap>()
		mapGasList.each{
			int flag = GasReportService.isExistHash(mapNoGasList,it.get("station"))
			def map = new HashMap()
			map.put("area",it.get("area"))
			map.put("station",it.get("station"))
			map.put("gasSale",it.get("gasSale"))
			map.put("fuelSale",it.get("fuelSale"))
			map.put("AllSale",it.get("AllSale"))
			map.put("monthFinish",it.get("monthFinish"))
			map.put("yearFinish",it.get("yearFinish"))
			if(flag == 1){
				mapNoGasList.each{n->
					if(it.get("station")==n.get("station")){
						map.put("noGasAll",n.get("noGasAll"))
						map.put("noGasMaoLi",n.get("noGasMaoLi"))
						map.put("noGasMonthFinish",n.get("noGasMonthFinish"))
						map.put("noGasYearFinish",n.get("noGasYearFinish"))
						mapNoGasAndGas.add(map)
					}
				}
			}
			else{
				map.put("noGasAll", 0)
				map.put("noGasMaoLi", 0)
				map.put("noGasMonthFinish", 0)
				map.put("noGasYearFinish", 0)
				mapNoGasAndGas.add(map)
			}
		}
		println "return:GasList"+ "${mapGasList}"
		println "return:NoGasList"+ "${mapNoGasList}"
		println "return:mapNoGasAndGas"+ "${mapNoGasAndGas}" 
		def cardPropList = GasReportService.cardPropReport(startDay ,endDay)
		use(TimeCategory) {
			endDay = endDay + 1.days
		}
		def cardResult = GasReportService.cardReport(startDay , endDay, year, endDay,"m")
		def mapGasCard = new ArrayList<HashMap>()
		if(cardResult!=0 && cardPropList!=0){
			cardResult.each{
				int flag = GasReportService.isExist(cardPropList,it[0],0)
				def map = new HashMap()
				map.put("area",it[0].stationArea)
				map.put("station",it[0])
				map.put("cardSale",it[1])
				map.put("cardMonthFinish",it[6])
				map.put("cardYearFinish",it[7])
				if(flag == 1){
					cardPropList.each{c->
					if(it[0]==c[0]){
						map.put("cardProp",c[3])
						mapGasCard.add(map)
						}
					}
				}
				else{
					map.put("cardProp",0)
					mapGasCard.add(map)
				}
			}
			println "mapGasCard:" +"${mapGasCard}"
		}
		println "mapNoGasAndGas:" +"${mapNoGasAndGas}"
		def mapAllList = new ArrayList<HashMap>()
		mapNoGasAndGas.each{
			int flag = GasReportService.isExistHash(mapGasCard,it.get("station"))
			def map = new HashMap()
			map.put("area",it.get("area"))
			map.put("station",it.get("station"))
			map.put("gasSale",it.get("gasSale"))
			map.put("fuelSale",it.get("fuelSale"))
			map.put("AllSale",it.get("AllSale"))
			map.put("monthFinish",it.get("monthFinish"))
			map.put("yearFinish",it.get("yearFinish"))
			map.put("noGasAll",it.get("noGasAll"))
			map.put("noGasMaoLi",it.get("noGasMaoLi"))
			map.put("noGasMonthFinish",it.get("noGasMonthFinish"))
			map.put("noGasYearFinish",it.get("noGasYearFinish"))
			if(flag == 1){
				mapGasCard.each{c->
				if(it.get("station")==c.get("station")){
					map.put("cardSale",c.get("cardSale"))
					map.put("cardMonthFinish",parseNumber(c.get("cardMonthFinish").toString(),1))
					map.put("cardYearFinish",parseNumber(c.get("cardYearFinish").toString(),1))
					map.put("cardProp",parseNumber(c.get("cardProp").toString(),1))
					mapAllList.add(map)
					}
				}
			}
			else{
				map.put("cardSale",0)
				map.put("cardMonthFinish",0)
				map.put("cardYearFinish",0)
				map.put("cardProp",0)
				mapAllList.add(map)
			}
		}
		mapAllList.sort{
			-it.get("AllSale")
		}
		return mapAllList
	}
	
def monthExportReport={
	def month,startDay,endDay
	def areaIds = []
	def mapList
	def selectType = Long.parseLong("0")
	if (params.month&&params.stationArea){
		month = parseDate(params.month)
		def stationArea = params.stationArea
		if(stationArea instanceof String) {
			areaIds.add(Long.parseLong(stationArea))
		} else {
			stationArea.each {id->
				areaIds.add(Long.parseLong(id))
			}
		}
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
		response.setHeader("Content-disposition", "attachment; filename=MonthReport.xls")
		mapList = monthGasReport(month[0], month[1], month[2], areaIds, selectType)
		def mapAllList = new ArrayList<HashMap>()
		mapList.each {
			def map = new HashMap()
			map.put("area",it.get("area"))
			map.put("station",it.get("station").shortName)
			map.put("gasSale",it.get("gasSale"))
			map.put("fuelSale",it.get("fuelSale"))
			map.put("AllSale",it.get("AllSale"))
			map.put("monthFinish",it.get("monthFinish"))
			map.put("yearFinish",it.get("yearFinish"))
			map.put("noGasAll",it.get("noGasAll"))
			map.put("noGasMaoLi",it.get("noGasMaoLi"))
			map.put("noGasMonthFinish",it.get("noGasMonthFinish"))
			map.put("noGasYearFinish",it.get("noGasYearFinish"))
			map.put("cardSale",it.get("cardSale"))
			map.put("cardMonthFinish",it.get("cardMonthFinish"))
			map.put("cardYearFinish",it.get("cardYearFinish"))
			map.put("cardProp",it.get("cardProp"))
			mapAllList.add(map)
		}
		List fields = ['area','station','gasSale','fuelSale','AllSale','monthFinish','yearFinish','noGasAll','noGasMaoLi','noGasMonthFinish','noGasYearFinish','cardSale','cardProp','cardMonthFinish','cardYearFinish']
		Map labels = ['area':"片区",'station':"站名", 'gasSale':"汽油(吨)",'fuelSale':"柴油(吨)",'AllSale':"合计(吨)",'monthFinish':"月度完成率",'yearFinish':"年度完成率",'noGasAll':"非油销售收入",'noGasMaoLi':"毛利",'noGasMonthFinish':"月度完成率",'noGasYearFinish':"年度完成率",'cardSale':"本月发卡量",'cardMonthFinish':"月度完成率",'cardYearFinish':"年度完成率",'cardProp':"月度卡销比"]
		exportService.export("excel", response.outputStream, mapAllList, fields, labels,[:],[:])

	}
}

/**
 * 油品销量月报表显示/导出/分析
 */
	def report = {
		def result
		def startDay = new Date()
		def endDay = new Date()
		def areaIds = []
		def selectType
		def gasTypeList = GasType.executeQuery("select g from GasType g where g.active=true and g.code<>'300566'")
		if(params.selectType instanceof String) {
			selectType = [Long.parseLong(params.selectType)]
		} else {
			selectType = params.selectType.collect {Long.parseLong(it)}
		}
		if (params.startDay&& params.endDay&&params.selectType&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = gasReport(startDay,endDay,selectType,areaIds)
			if (result==0){
				flash.message = "${message(code: 'default.nofound.message')}"
				redirect(action:"report")
			}
		}
		[startDay:startDay,endDay:endDay,result:result,selectType:selectType,stationArea:areaIds,gasTypeList:gasTypeList]
	}
	
	def planReport = {
		def result,planResult
		def startDay = new Date()
		def endDay = new Date()
		def areaIds = []
		def selectType
		def gasTypeList = GasType.executeQuery("select g from GasType g where g.active=true and g.code<>'300566'")
		if(params.selectType instanceof String) {
			selectType = [Long.parseLong(params.selectType)]
		} else {
			selectType = params.selectType.collect {Long.parseLong(it)}
		}
		if (params.startDay&& params.endDay&&params.selectType&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			planResult = GasPlanReport(startDay,endDay,areaIds,'M')
			if (planResult==0){
				flash.message = "${message(code: 'default.nofound.message')}"
				redirect(action:"planReport")
			}
		}
		[startDay:startDay,endDay:endDay,gpResult:planResult,result:result,selectType:selectType,stationArea:areaIds,gasTypeList:gasTypeList]
	}
		
	def gasExport = {
		def startDay,endDay
		def result,planResult
		def areaIds = []
		def selectType

		if (params.startDay&& params.endDay&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			if(params.selectType instanceof String) {
				selectType = [Long.parseLong(params.selectType)]
			} else {
				selectType = params.selectType.collect {Long.parseLong(it)}
			}
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=GasReport.xls")
			result = gasReport(startDay,endDay,selectType,areaIds)
			def mapList = new ArrayList<HashMap>()
			def typeSize = selectType.size()
			result.each {
				def map = new HashMap()
				def i = 2
				map.put("area", it[0])
				map.put('stationName', it[1].name)
				selectType.each {st->
					map.put(st.toString(), parseNumber((it[i]/1000).toString(),1))
					i++
				}
				map.put('allNums', parseNumber((it[typeSize+2]/1000).toString(),1))
				mapList.add(map)
			}
			List fields = ['stationName','area']
			Map labels = ['stationName':"站名", 'area':"片区"]
			selectType.each {type->
				fields.add(type.toString())
				labels.put(type.toString(), GasType.get(type).shortName+"(吨)")
			}
			fields.add("allNums")
			labels.put("allNums", "总销量(吨)")
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
	}
	
	def gasPlanExport = {
		def startDay,endDay
		def result,planResult
		def areaIds = []
		def selectType

		if (params.startDay&& params.endDay&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			if(params.selectType instanceof String) {
				selectType = [Long.parseLong(params.selectType)]
			} else {
				selectType = params.selectType.collect {Long.parseLong(it)}
			}
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=GasPlanReport.xls")
			planResult = GasPlanReport(startDay,endDay,areaIds,'M')
			def mapPlanList = new ArrayList<HashMap>()
			planResult.each {
				def mapPlan = new HashMap()
				mapPlan.put("area", it[0])
				mapPlan.put('stationName', it[1].name)
				mapPlan.put('gasPlanNums', it[2])
				mapPlan.put('fuelPlanNums', it[3])
				mapPlan.put('retailPlanNums', it[4])
				mapPlan.put('gasNums', it[5])
				mapPlan.put('fuelNums', it[6])
				mapPlan.put('retailNums', it[7])
				mapPlan.put('PlanRate', it[8])
				mapPlanList.add(mapPlan)
			}
			List fields = ['area','stationName','gasPlanNums','fuelPlanNums','retailPlanNums','gasNums','fuelNums','retailNums','PlanRate']
			Map labels = ['area':"片区",'stationName':"站名", 'gasPlanNums':"汽油计划量",'fuelPlanNums':"柴油计划量",'retailPlanNums':"计划总量",'gasNums':"汽油销量",'fuelNums':"柴油销量",'retailNums':"总销量",'PlanRate':"计划完成量%"]
			exportService.export("excel", response.outputStream, mapPlanList, fields, labels,[:],[:])
		}
	}
	

	def gasReport(Date startDay, Date endDay,selectType,areaIds) {
		def result
		if 	(startDay>endDay){
			flash.message = "开始时间不能大于结束时间"
			return 0
		}
		if (selectType==0) selectType = GasType.executeQuery("select g.id from GasType g where g.active=true and g.code<>'300566'")
		def typeSize = selectType.size()
		def sb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		selectType.each {typeId->
			def type = GasType.get(typeId)
			if (typeId ==1){
				sb.append ", sum(case when gs.gasCode = '300566' or gs.gasCode = '300472' then gs.saleNumber else 0 end) "
			}
			else if(typeId == 6){
				sb.append ", sum(case when gs.gasCode = '402097' or gs.gasCode = '300050' then gs.saleNumber else 0 end) "
			}
			else sb.append ", sum(case when gs.gasCode = '${type.code}' then gs.saleNumber else 0 end) "
		}
		sb.append '''
				, sum(gs.saleNumber)
				from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds) group by gs.station'''
		result = GasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (!result){
			flash.message = "${message(code: 'default.nofound.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
			return 0
		}		
		result.each{r->
			float t = 0
			(1.. typeSize).each{i->
				int k = i+1
				t += r[k]
			} 
			r[typeSize+2] = t
		}
		result.sort{
			-it[typeSize+2]
		}
		return result
	}
	
/**
 * 油品计划完成量（片区，站名，汽油计划量，柴油计划量，计划总量， 汽油完成量，柴油完成量，总销量，计划完成率）	
 * @param startDay
 * @param endDay
 * @param areaIds
 * @return
 */
	def GasPlanReport(Date startDay, Date endDay,areaIds,type) {
		println "${endDay},${startDay}"
		def endYear = endDay
		use(TimeCategory) {
			if (type=="Y")	endYear = startDay + 12.months
			}
		def syb = new StringBuilder("select gpys.station.stationArea.areaName,gpys.station")
		syb.append '''
				, sum(gpys.gasolineAmount)
				, sum(gpys.fuelAmount)
				, sum(gpys.retailAmount)
				, sum(gpys.retailAmount)
				, sum(gpys.retailAmount)
				, sum(gpys.retailAmount)
				, sum(gpys.retailAmount)
				from GasPlanYearSum gpys where gpys.planTime <= :endDay and gpys.planTime >= :startDay and gpys.station.stationArea.id in(:areaIds) group by gpys.station'''
		def gypResult = GasPlanYearSum.executeQuery(syb.toString(),['startDay':startDay, 'endDay':endYear,'areaIds':areaIds])
		def sb = new StringBuilder("select gps.station.stationArea.areaName,gps.station")
		sb.append '''
				, sum(gps.gasolineAmount)
				, sum(gps.fuelAmount)
				, sum(gps.retailAmount)
				, sum(gps.retailAmount)
				, sum(gps.retailAmount)
				, sum(gps.retailAmount)
				, sum(gps.retailAmount)
				from GasPlanSum gps where gps.planTime <= :endDay and gps.planTime >= :startDay and gps.station.stationArea.id in(:areaIds) group by gps.station'''
		def gpResult = GasPlanSum.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endYear,'areaIds':areaIds])
		if (gypResult&&type == "Y"){
			gpResult = gypResult
		}
		def spb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		def categories = GasTypeCategory.list()
		def hql = "select gs.station.stationArea.areaName,gs.station"
		categories.each {cat->
			hql += ",sum(case when gs.type.category.id = ${cat.id} then gs.saleNumber else 0 end)"
		}
		hql += ",sum(saleNumber) from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds) group by gs.station"
		def result = GasSale.executeQuery(hql,['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (!result||!gpResult){
			flash.message = "${message(code: 'default.nofound.message')}"
			return 0
		}
		def gasAmount = new HashMap()
		def fuelAmount = new HashMap()
		def retailAmount = new HashMap()
		result.each {
			gasAmount.put(it[1], it[3]/1000)
			fuelAmount.put(it[1], it[2]/1000)
			retailAmount.put(it[1], it[4]/1000)
		}
		gpResult.each {
			it[5] = gasAmount.get(it[1])?gasAmount.get(it[1]):0
			it[6] = fuelAmount.get(it[1])?fuelAmount.get(it[1]):0
			it[7] = retailAmount.get(it[1])?retailAmount.get(it[1]):0
			if (it[4]!=null&&it[7]!=0){
				it[8] = it[7]/it[4]*100
			}
			else{
				it[8] = 0
			}
		}
		gpResult.sort {
			-it[8]
		}
		return gpResult
	}
	
/**
 * 油品月度完成率统计(不分站)
 */
	def gasMFinishRate(Date startDay,Date endDay,areaIds){
		def result,gpResult,rate
		println "${startDay},${endDay},${areaIds}"
		def sb = new StringBuilder("select sum(gps.retailAmount) from GasPlanSum gps where gps.planTime <= :endDay and gps.planTime >= :startDay and gps.station.stationArea.id in(:areaIds) ")
		gpResult = GasPlanSum.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		def spb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		def hql = "select sum(saleNumber) from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds)"
		result = GasSale.executeQuery(hql,['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (result[0]&&gpResult[0]) rate = parseNumber((result[0]/10/gpResult[0]).toString(), 1)
		else if (!gpResult[0]) rate=0
		else rate = 100
		return rate
	}
	
	def gasYFinishRate(Date startDay,Date endDay,Date end,areaIds){
		def result,gpResult,gypResult,rate
		println "${startDay},${endDay},${end},${areaIds}"
		def sb = new StringBuilder("select sum(gps.retailAmount) from GasPlanSum gps where gps.planTime <= :endDay and gps.planTime >= :startDay and gps.station.stationArea.id in(:areaIds) ")
		gpResult = GasPlanSum.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		def syb = new StringBuilder("select sum(gpys.retailAmount) from GasPlanYearSum gpys where gpys.planTime <= :endDay and gpys.planTime >= :startDay and gpys.station.stationArea.id in(:areaIds) ")
		gypResult = GasPlanYearSum.executeQuery(syb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (gypResult[0]) gpResult = gypResult
		def spb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		def hql = "select sum(saleNumber) from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds)"
		result = GasSale.executeQuery(hql,['startDay':startDay, 'endDay':end,'areaIds':areaIds])
		if (result[0]&&gpResult[0]) rate = parseNumber((result[0]/10/gpResult[0]).toString(), 1)
		else if (!gpResult[0]) rate=0
		else rate = 100
		return rate
	}
	
	/**
	 * 非油品月度完成率统计(不分站)
	 */
		def noGasMFinishRate(Date startDay,Date endDay,areaIds){
			def result,gpResult,rate
			println "${startDay},${endDay},${areaIds}"
			def sb = new StringBuilder("select sum(ngps.planNums) from NoGasPlanSum ngps where ngps.planTime <= :endDay and ngps.planTime >= :startDay and ngps.station.stationArea.id in(:areaIds) ")
			gpResult = NoGasPlanSum.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
			def spb = new StringBuilder("select ngs.station.stationArea.areaName,ngs.station")
			def hql = "select sum(income) from NoGasSale ngs where ngs.saleTime <= :endDay and ngs.saleTime >= :startDay and ngs.station.stationArea.id in(:areaIds)"
			result = NoGasSale.executeQuery(hql,['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
			if (result[0]&&gpResult[0]) rate = parseNumber((result[0]/gpResult[0]*100).toString(), 1)
			else if (!gpResult[0]) rate=0
			else rate = 100
			return rate
		}
		
		def noGasYFinishRate(Date startDay,Date endDay,Date end,areaIds){
			def result,gpResult,gypResult,rate
			println "${startDay},${endDay},${end},${areaIds}"
			def sb = new StringBuilder("select sum(ngps.planNums) from NoGasPlanSum ngps where ngps.planTime <= :endDay and ngps.planTime >= :startDay and ngps.station.stationArea.id in(:areaIds) ")
			gpResult = NoGasPlanSum.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
			def spb = new StringBuilder("select ngs.station.stationArea.areaName,ngs.station")
			def hql = "select sum(income) from NoGasSale ngs where ngs.saleTime <= :endDay and ngs.saleTime >= :startDay and ngs.station.stationArea.id in(:areaIds)"
			result = NoGasSale.executeQuery(hql,['startDay':startDay, 'endDay':end,'areaIds':areaIds])
			if (result[0]&&gpResult[0]) rate = parseNumber((result[0]/gpResult[0]*100).toString(), 1)
			else if (!gpResult[0]) rate=0
			else rate = 100
			return rate
		}

/**
 * 油品销量同比环比查询	显示、导出、分析
 */
	def compareGas = {
		def result
		def areaIds = []
		def selectType
		def startDay = new Date()
		def endDay = new Date()
		def startDayA = new Date()
		def endDayA = new Date()
		def gasTypeList = GasType.executeQuery("select g from GasType g where g.active=true and g.code<>'300566'")
		if (params.startDay&& params.endDay&&params.startDayA&& params.endDayA&&params.selectType&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			startDayA = params.startDayA
			endDayA = params.endDayA
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			if(params.selectType instanceof String) {
				selectType = [Long.parseLong(params.selectType)]
			} else {
				selectType = params.selectType.collect {Long.parseLong(it)}
			}
			result = compareGasReport(startDay,endDay,startDayA,endDayA,selectType,areaIds)
		}
		[result:result,startDay:startDay,endDay:endDay,startDayA:startDayA,endDayA:endDayA,selectType:selectType,stationArea:areaIds,gasTypeList:gasTypeList]
	}
	
	def compareGasExport = {
		def startDay,endDay,startDayA,endDayA
		def areaIds = []
		def selectType
		def result
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
		response.setHeader("Content-disposition", "attachment; filename=compareGas.xls")
		if (params.startDay&& params.endDay&&params.startDayA&& params.endDayA&&params.selectType&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			startDayA = params.startDayA
			endDayA = params.endDayA
			if(params.selectType instanceof String) {
				selectType = [Long.parseLong(params.selectType)]
			} else {
				selectType = params.selectType.collect {Long.parseLong(it)}
			}
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			} 
			result = compareGasReport(startDay,endDay,startDayA,endDayA,selectType,areaIds)
			def mapList = new ArrayList<HashMap>()
			result.each{
				def map = new HashMap()
				map.put("area", it.get("area"))
				map.put("station", it.get("station"))
				selectType.each {type->
					map.put((type+"A"), it.get(GasType.get(type).name)[0])
					map.put((type+"B"), it.get(GasType.get(type).name)[1])
				}
				map.put("allNumsA", it.get("allNums")[0])
				map.put("allNumsB", it.get("allNums")[1])
				map.put("compareNums", it.get("compareNums"))
				mapList.add(map)
			}
			List fields = ['station','area']
			Map labels = [ 'area':"片区",'station':"站名"]
			selectType.each {type->
				fields.add((type+"A"))
				labels.put((type+"A"), "前-"+GasType.get(type).shortName)
				fields.add((type+"B"))
				labels.put((type+"B"), "后-"+GasType.get(type).shortName)			
				}
			fields.add("allNumsA")
			labels.put("allNumsA", "前-总销量(吨)")
			fields.add("allNumsB")
			labels.put("allNumsB", "后-总销量(吨)")
			fields.add("compareNums")
			labels.put("compareNums", "差值(吨)")
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
	}
	
	def compareGasReport(Date startDay, Date endDay,Date startDayA, Date endDayA,selectType,areaIds){
		def result,cResult,endDay1,endDayA1
		if 	(startDay>endDay||startDayA>endDayA){
			flash.message = "开始时间不能大于结束时间"
			redirect(action: "compareGas")
		}
		def sb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		def typeSize = selectType.size()
		selectType.each {typeId->
			def type = GasType.get(typeId)
			if (typeId ==1){
				sb.append ", sum(case when gs.gasCode = '300566' or gs.gasCode = '300472' then gs.saleNumber else 0 end) "
			}
			else if(typeId == 6){
				sb.append ", sum(case when gs.gasCode = '402097' or gs.gasCode = '300050' then gs.saleNumber else 0 end) "
			}
			else sb.append ", sum(case when gs.gasCode = '${type.code}' then gs.saleNumber else 0 end) "
		}
		sb.append '''
				, sum(gs.saleNumber)
				from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds) group by gs.station
				order by sum(gs.saleNumber) DESC'''
		result = GasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		result.each{r->
			float t = 0
			(1.. typeSize).each{i->
				int k = i+1
				t += r[k]
			} 
			r[typeSize+2] = t
		}
		
		def csb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		selectType.each {typeId->
			def type = GasType.get(typeId)
			if (typeId ==1){
				csb.append ", sum(case when gs.gasCode = '300566' or gs.gasCode = '300472' then gs.saleNumber else 0 end) "
			}
			else csb.append ", sum(case when gs.gasCode = '${type.code}' then gs.saleNumber else 0 end) "
		}
		csb.append '''
				, sum(gs.saleNumber)
				from GasSale gs where gs.saleTime <= :endDayA and gs.saleTime >= :startDayA and gs.station.stationArea.id in(:areaIds) group by gs.station
				order by sum(gs.saleNumber) DESC'''
		cResult = GasSale.executeQuery(csb.toString(),['startDayA':startDayA, 'endDayA':endDayA,'areaIds':areaIds])
		cResult.each{r->
			float t = 0
			(1.. typeSize).each{i->
				int k = i+1
				t += r[k]
			}
			r[typeSize+2] = t
		}
		if (!result||!cResult){
			flash.message = "${message(code: 'default.nofound.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
			redirect(action: "compareGas")
		}
		def cGasSale = new HashMap()
		cResult.each{
			cGasSale.put(it[1],it)
		}
		def resultList = new ArrayList<HashMap>()
 
		result.each {l1->
			def station = l1[1]
			def area = l1[0]
			def l2 = cGasSale.get(station)
			def map = new HashMap()
			map.put("area", area)
			map.put("station", station.shortName)
			def i=0
			def ll1,ll2,lll1,lll2
			selectType.each {k->
				ll1 = l1!=null?l1[i+2]:0
				ll2 = l2!=null?l2[i+2]:0
				lll1 = l1!=null?l1[typeSize+2]:0
				lll2 = l2!=null?l2[typeSize+2]:0
				map.put(GasType.get(k).name, [parseNumber((ll1/1000).toString(),1), parseNumber((ll2/1000).toString(),1)])
				i++
			}
			map.put("allNums",[parseNumber((lll1/1000).toString(),1), parseNumber((lll2/1000).toString(),1)])
			def compareNums = lll1-lll2
			def compare = parseNumber((compareNums/1000).toString(),1)
			map.put("compareNums",compare)
			resultList.add(map)
		}
		resultList.sort() {map->
			-map.get("compareNums")
		}
		return resultList
	}
	
	/**
	 * 处理float数值，最大保留3位
	 * @param s
	 * @param i
	 * @return
	 */
	def parseNumber(String s,int i){
		NumberFormat nf = NumberFormat.getInstance();
		float d = Float.valueOf(s);
		nf.setMinimumFractionDigits(i);
		nf.setMaximumFractionDigits(i);
		def number = nf.parse(nf.format(d)).floatValue()
		return number
	}
	
/**
 * 柴汽比显示、导出、分析
 */
	def gasFuel ={
		def startDay = new Date()
		def endDay = new Date()
		def result,planResult
		def areaIds = []
		if (params.startDay&& params.endDay&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = gasFuelReport(startDay,endDay,areaIds)
			if (result == []){
				flash.message = "${message(code: 'default.nofound.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
				redirect(action: "gasFuel")
			}
		}
		[startDay:startDay,endDay:endDay,result:result,stationArea:areaIds]
	}
	
	def gasFuelExport ={
		def startDay,endDay
		def result,planResult
		def areaIds = []
		if (params.startDay&& params.endDay&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = gasFuelReport(startDay,endDay,areaIds)
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=gasFuel.xls")
			def mapPlanList = new ArrayList<HashMap>()
			result.each {
				def mapPlan = new HashMap()
				mapPlan.put("area", it[0])
				mapPlan.put("stationName", it[1].name)
				mapPlan.put('gasNums', parseNumber((it[2]/1000).toString(),1))
				mapPlan.put('fuelNums', parseNumber((it[3]/1000).toString(),1))
				mapPlan.put('retailNums', parseNumber((it[4]/1000).toString(),1))
				mapPlan.put('gasFuelRate', parseNumber((it[5]).toString(),2))
				mapPlanList.add(mapPlan)
			}
			List fields = ['area','stationName','gasNums','fuelNums','retailNums','gasFuelRate']
			Map labels = ['area':"片区",'stationName':"站名", 'gasNums':"柴油销量(吨)",'fuelNums':"汽油销量(吨)",'retailNums':"总销量(吨)",'gasFuelRate':"柴汽比"]
			exportService.export("excel", response.outputStream, mapPlanList, fields, labels,[:],[:])
			}
	} 
	
	def gasFuelReport(Date startDay, Date endDay,stationArea) {
		def result
		def categories = GasTypeCategory.list()
		def hql = "select gs.station.stationArea.areaName,gs.station"
		categories.each {cat->
			hql += ",sum(case when gs.type.category.id = ${cat.id} then gs.saleNumber else 0 end)"
		}
		hql += ",sum(saleNumber),sum(saleNumber) from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds) group by gs.station"
		result = GasSale.executeQuery(hql,['startDay':startDay, 'endDay':endDay,'areaIds':stationArea])
		def cat_len = categories.size()
		result.each {
			if (it[3]!=0) it[cat_len+3]=it[2]/it[3]
			else it[cat_len+3]=0
		}
		result.sort{
			-it[cat_len+3]
		}
		return result
	}
	
/**
 * 	单站日销量显示、导出、分析
 */
	def dayStation = {
		def result
		def startDay = new Date()
		def endDay = new Date()
		def areaIds = []
		def selectType
		def gasTypeList = GasType.executeQuery("select g from GasType g where g.active=true and g.code<>'300566'")
		if(params.selectType instanceof String) {
			selectType = [Long.parseLong(params.selectType)]
		} else {
			selectType = params.selectType.collect {Long.parseLong(it)}
		}
		if (params.startDay&& params.endDay&&params.selectType&&params.stationArea){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = dayStationReport(startDay,endDay,selectType,areaIds)
		}
		[result:result,startDay:startDay,endDay:endDay,selectType:selectType,stationArea:areaIds,gasTypeList:gasTypeList]
	}
	
	def dayStationExport = {
		def result
		def areaIds = []
		def startDay,endDay
		def selectType
		if(params.selectType instanceof String) {
			selectType = [Long.parseLong(params.selectType)]
		} else {
			selectType = params.selectType.collect {Long.parseLong(it)}
		}
		def stationArea = params.stationArea
		if(stationArea instanceof String) {
			areaIds.add(Long.parseLong(stationArea))
		} else {
			stationArea.each {id->
				areaIds.add(Long.parseLong(id))
			}
		}
		
		def typeSize = selectType.size()
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
		response.setHeader("Content-disposition", "attachment; filename=dayStation.xls")
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			result = dayStationReport(startDay,endDay,selectType,areaIds)
			def mapList = new ArrayList<HashMap>()
			result.each {
				def map = new HashMap()
				map.put("stationName", it[0])
				map.put("area", it[1].name)
				def i = 2
				selectType.each {st->
					map.put(st.toString(), parseNumber((it[i]/1000).toString(),1))
					i++
				}
				map.put('days', it[typeSize+3])
				map.put('averNums', parseNumber((it[typeSize+4]/1000).toString(),1))
				mapList.add(map)
			}
			List fields = ['stationName','area']
			Map labels = ['stationName':"站名", 'area':"片区"]
			selectType.each {type->
				fields.add(type.toString().toString())
				labels.put(type.toString(), GasType.get(type).shortName+"(吨)")
			}
			fields.add("days")
			labels.put("days", "营业天数")
			fields.add("averNums")
			labels.put("averNums", "单站日销量(吨)")
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
	}

	def dayStationReport(Date startDay, Date endDay,selectType,stationArea){
		def result,gpResult,sResult
		if 	(startDay>endDay){
			flash.message = "开始时间不能大于结束时间"
			redirect(action: "dayStation")
		}
		def sb = new StringBuilder("select gs.station.stationArea.areaName,gs.station")
		selectType.each {typeId->
			def type = GasType.get(typeId)
			if (typeId ==1){
				sb.append ", sum(case when gs.gasCode = '300566' or gs.gasCode = '300472' then gs.saleNumber else 0 end) "
			}
			else sb.append ", sum(case when gs.gasCode = '${type.code}' then gs.saleNumber else 0 end) "
		}
		sb.append '''
				, sum(gs.saleNumber) 
				, sum(gs.saleNumber) 
				, sum(gs.saleNumber) 
				from GasSale gs where gs.saleTime <= :endDay and gs.saleTime >= :startDay and gs.station.stationArea.id in(:areaIds) group by gs.station'''
		result = GasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':stationArea])
		def days = (endDay.time-startDay.time)/(1000*60*60*24)+1
		def typeSize = selectType.size()
		result.each{r->
			float t = 0
			(1.. typeSize).each{i->
				int k = i+1
				t += r[k]
			}
			r[typeSize+2] = t
		}
		if (!result){
			flash.message = "${message(code: 'default.nofound.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
			redirect(action: "dayStation")
		}
		String sto = '''select sto.station,count(sto.stopDay) from StationOpenTime sto 
						where sto.stopDay <= :endDay and sto.stopDay >= :startDay group by sto.station'''
		sResult = StationOpenTime.executeQuery(sto.toString(),['startDay':startDay, 'endDay':endDay])
		def daySale = new HashMap()
		result.each {
			it[typeSize+3] = days
			sResult.each{s->
				if(s[0]==it[1]){
					it[typeSize+3] = days - s[1]
				}
			}
			it[typeSize+4] = it[typeSize+2]/it[typeSize+3]
		}
		
		result.sort {
			-it[typeSize+4]
		}
		println result
		return result
	}
	
/**
 * 6.28-7.17.XLS用utf-16编码导入，0715加油站支付日报.XLS用GBK导入
 */
	def ImportExcelService
	def springSecurityService
	def gasListImport = {
		int fromLine = 3
		def file = params.gasCustomerList
		def fileName = file.originalFilename
		
		int lineNum = 0
		BufferedReader reader = new BufferedReader(new InputStreamReader(file.inputStream, "utf-16"))

			reader.eachLine {line->
				println line
			if(lineNum > fromLine) {
				String[] words = line.split()
				if(words.length > 21) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd")
					Date saleDate = new Date()
					saleDate = sdf.parse(words[0])
					OwnStation ownStation = OwnStation.findByName("${words[2]}")
					
					if (ownStation){
						GasList gasList = GasList.createCriteria().get {
							eq("gasCode", words[3])
							eq("boardPrice", words[6])
							eq("stationCode", words[1])
							eq("saleTime", saleDate)
						}
						if(!gasList) {
							gasList = new GasList()
							gasList.stationCode = words[1]
							gasList.gasCode = words[3]
							gasList.saleTime = saleDate
							gasList.boardPrice = words[6]
							gasList.station = ownStation
						}
						gasList.gasType = words[4]+words[5]
						gasList.saleNumber = words[7]
						gasList.converWeight = words[8]
						gasList.actualAmount = words[9]
						gasList.cashAmount = words[10]
						gasList.bankCardAmount = words[11]
						gasList.icCardAmount = words[12]
						gasList.gasCardAmount = words[13]
						gasList.integrationAmount = words[14]
						gasList.otherAmount = words[15]
						gasList.cashNumber = words[16]
						gasList.bankCardNumber = words[17]
						gasList.icCardNumber = words[18]
						gasList.gasCardNumber = words[19]
						gasList.integrationNumber = words[20]
						gasList.otherNumber = words[21]
						gasList.weightDensity = words[22]
						gasList.save()
						println gasList
						
						GasSale gasSale = GasSale.createCriteria().get {
							eq("gasCode", words[3])
							eq("boardPrice", words[6])
							eq("station", ownStation)
							eq("saleTime", saleDate)
						}
						
						if(!gasSale) {
							gasSale = new GasSale()
							gasSale.gasCode = words[3]
							gasSale.saleTime = saleDate
							gasSale.boardPrice = words[6]
							gasSale.station = ownStation
							gasSale.type = GasType.findByCode(words[3])
						}
						float saleNumber=Float.parseFloat("${words[8].replaceAll(',','').replaceAll('"','')}")
						float icCardNumber=Float.parseFloat("${words[18].replaceAll(',','').replaceAll('"','')}")
						float actualAmount=Float.parseFloat("${words[9].replaceAll(',','').replaceAll('"','')}")
						float icCardAmount=Float.parseFloat("${words[12].replaceAll(',','').replaceAll('"','')}")
						gasSale.saleNumber = saleNumber 
						gasSale.icCardNumber = icCardNumber 
						gasSale.actualAmount = actualAmount 
						gasSale.icCardAmount = icCardAmount 
						gasSale.save()
						println gasSale
						
						GasDensity density = new GasDensity()
						density.type = GasType.findByCode(words[3])
						density.density = Float.parseFloat("${words[22].replaceAll(',','')}")
						density.time = saleDate
						density.save()
						println density
						flash.message = "${message(code: 'default.updateFileSuccess.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
					}
				}
			}
			lineNum++
		}
		ImportExcel excel = ImportExcelService.storeFile(file.inputStream,fileName)
		excel.uploader = springSecurityService.principal.username
		excel.type = "油品销售清单"
		excel.save()
		redirect(action:"list")
	}
	
	def exchangeDensity = {
		GasList.list().each{
			GasDensity density = new GasDensity()
			density.type = GasType.findByCode(it.gasCode)
			density.density = Float.parseFloat("${it.weightDensity.replaceAll(',','')}")
			density.time = it.saleTime
			density.save(flash:true)
			println density
		}
	}
	
	def changeGasSale = {
		def gasList = GasList.list()
		gasList.each{
			println it
			float saleNumber=Float.parseFloat("${it.converWeight.replaceAll(',','')}")
			float icCardNumber=Float.parseFloat("${it.icCardNumber.replaceAll(',','')}")
			float actualAmount=Float.parseFloat("${it.actualAmount.replaceAll(',','')}")
			float icCardAmount=Float.parseFloat("${it.icCardAmount.replaceAll(',','')}")
			GasSale gasSale = new GasSale(
				gasCode:"${it.gasCode}",saleNumber:"${saleNumber}",icCardNumber:"${icCardNumber}",boardPrice:"${it.boardPrice}",
				actualAmount:"${actualAmount}",icCardAmount:"${icCardAmount}",
				)
			gasSale.type = GasType.findByCode(it.gasCode)
			gasSale.station = OwnStation.findByName(it.station.name)
			gasSale.saleTime = it.saleTime
			gasSale.save()
			println gasSale
		}
	}
	
    def list = {
        params.max = Math.min(params.max ? params.int('max') : 10, 100)
		def sdf = new SimpleDateFormat('yyyy-MM-dd')
		def dateFrom = params.dateFrom? sdf.parse(params.dateFrom):null
		def dateTo = params.dateTo? sdf.parse(params.dateTo):null
		def station = params['station.id']?OwnStation.get(params['station.id']):null
		def result = GasList.createCriteria().list ([max:params.max, offset:params.offset]){
			if(station){
				eq("station",station)
			}
			if(dateFrom) {
				ge("saleTime", dateFrom)
			}
			if(dateTo) {
				le("saleTime", dateTo)
			}
			order("saleTime","desc")
		}
        [gasListInstanceList: result, gasListInstanceTotal: result.totalCount]
    }

    def create = {
        def gasListInstance = new GasList()
        gasListInstance.properties = params
        return [gasListInstance: gasListInstance]
    }

    def save = {
        def gasListInstance = new GasList(params)
        if (gasListInstance.save(flush: true)) {
            flash.message = "${message(code: 'default.created.message', args: [message(code: 'gasList.label', default: 'GasList'), gasListInstance.id])}"
            redirect(action: "show", id: gasListInstance.id)
        }
        else {
            render(view: "create", model: [gasListInstance: gasListInstance])
        }
    }

    def show = {
        def gasListInstance = GasList.get(params.id)
        if (!gasListInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'gasList.label', default: 'GasList'), params.id])}"
            redirect(action: "list")
        }
        else {
            [gasListInstance: gasListInstance]
        }
    }

    def edit = {
        def gasListInstance = GasList.get(params.id)
        if (!gasListInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'gasList.label', default: 'GasList'), params.id])}"
            redirect(action: "list")
        }
        else {
            return [gasListInstance: gasListInstance]
        }
    }

    def update = {
        def gasListInstance = GasList.get(params.id)
        if (gasListInstance) {
            if (params.version) {
                def version = params.version.toLong()
                if (gasListInstance.version > version) {
                    
                    gasListInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'gasList.label', default: 'GasList')] as Object[], "Another user has updated this GasList while you were editing")
                    render(view: "edit", model: [gasListInstance: gasListInstance])
                    return
                }
            }
            gasListInstance.properties = params
            if (!gasListInstance.hasErrors() && gasListInstance.save(flush: true)) {
                flash.message = "${message(code: 'default.updated.message', args: [message(code: 'gasList.label', default: 'GasList'), gasListInstance.id])}"
                redirect(action: "show", id: gasListInstance.id)
            }
            else {
                render(view: "edit", model: [gasListInstance: gasListInstance])
            }
        }
        else {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'gasList.label', default: 'GasList'), params.id])}"
            redirect(action: "list")
        }
    }

    def delete = {
        def gasListInstance = GasList.get(params.id)
        if (gasListInstance) {
            try {
                gasListInstance.delete(flush: true)
                flash.message = "${message(code: 'default.deleted.message', args: [message(code: 'gasList.label', default: 'GasList'), params.id])}"
                redirect(action: "list")
            }
            catch (org.springframework.dao.DataIntegrityViolationException e) {
                flash.message = "${message(code: 'default.not.deleted.message', args: [message(code: 'gasList.label', default: 'GasList'), params.id])}"
                redirect(action: "show", id: params.id)
            }
        }
        else {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'gasList.label', default: 'GasList'), params.id])}"
            redirect(action: "list")
        }
    }
}
